---
title: "SQL-PRV-01: Provider by provider ID"
summary: Retrieve a single provider by its surrogate ID
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
---

# SQL-PRV-01 Provider by provider ID

Retrieves a single `PROVIDER` record joined to its primary `PROVIDER_TAX_ID` record, using
the provider's surrogate key. This is the primary lookup used by `clsProvider.Retrieve`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-PRV-01 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (BOF criteria constant) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsProvider` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:ProviderID` | Input | Decimal | N/A | `PROVIDER.PROVIDER_SEQ_NUM` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER](../database-tables.md#provider) | SELECT | `PRIMARY_TAX_ID`, `PROVIDER_SEQ_NUM`, `UPDATE_DATE`, `UPDATE_NUM`, `UPDATE_USER` |
| [PROVIDER_TAX_ID](../database-tables.md#provider_tax_id) | JOIN (LEFT OUTER) | `TAX_ID`, `TAX_ID_DISPLAY_DESCR`, `REGISTERED_NAME` |

## Sample SQL

```sql
-- Provider by provider ID
SELECT
  PRV.PRIMARY_TAX_ID        AS PrimaryTaxID,
  PRV.PROVIDER_SEQ_NUM      AS ProviderID,
  PRV.UPDATE_DATE           AS UpdateDate,
  PRV.UPDATE_NUM            AS UpdateNum,
  PRV.UPDATE_USER           AS UpdateUser,
  TAX.TAX_ID_DISPLAY_DESCR  AS FormattedPrimaryTaxID,
  TAX.REGISTERED_NAME       AS RegisteredName
FROM PROVIDER PRV
  LEFT OUTER JOIN PROVIDER_TAX_ID TAX
    ON TAX.TAX_ID = PRV.PRIMARY_TAX_ID
WHERE PRV.PROVIDER_SEQ_NUM = :ProviderID
```

## Operations and processes
- [Retrieve provider by ID or tax ID](../operations/retrieve-provider-by-id-or-tax-id.md)

## Business rules
- [BR-PRV-001: Primary tax ID format](../business-rules.md#br-prv-001-primary-tax-id-must-be-a-9-digit-number)
- [BR-PRV-004: Provider retrieved read-only](../business-rules.md#br-prv-004-providers-retrieved-via-factory-methods-are-read-only)
- [BR-DATA-001: Read-only SQL properties](../business-rules.md#br-data-001-no-write-operations-are-supported)

## Sources
- SAIF.Business.View.Provider/clsProvider.vb